#!/usr/bin/python
#coding: utf-8

import sys
sys.path.append("..")

from Config.Config import conn, Create_Soft_Survey, Create_Soft_Info, Create_Classification, TRIGGER
from Logging.AppLogging import log_Main

class ApplicationMySql(object):

    def __init__(self):
        self.conn = conn
        self.conn.select_db("appchina")
        self.select_sql = "select url, name from pageurl order by id;"
        self.insert_sql_survey = "insert into soft_survey(url, name, comment, exp) values ('%s', '%s', '%s', '%s');"
        self.insert_sql_info = "insert into soft_info(url, name, size, _update, edition, _format, classification, requirement, jurisdiction) values('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"
        self.select_sql1 = "select url from soft_survey;"
        self.delete_sql = "delete from pageurl where url = '%s';"

    def GetUrl(self):
        self.cur = self.conn.cursor()
        self.cur.execute(self.select_sql)
        rows = self.cur.fetchall()
        # log_Main(u"所有链接从数据库中读取成功!!!", "info")
        return rows

    def CreateSoftSurvey(self):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        self.cur.execute(Create_Soft_Survey)
        # log_Main(u"创建数据库成功！！！！", "info")

    def CreateSoftInfo(self):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        self.cur.execute(Create_Soft_Info)
        # log_Main(u"创建数据库成功！！！！", "info")

    def CreateClassification(self):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        self.cur.execute(Create_Classification)

    def CreateSoftClassification(self):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        # log_Main(u"创建数据库成功！！！！", "info")

        # 同时创建这个表的触发器
        try:
            self.cur.execute(TRIGGER)

            self.conn.commit()
            print u"创建触发器成功！！！！"
        except Exception as e:
            self.conn.rollback()

    def Insert(self, args1, args2):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        try:
            self.cur.execute(self.insert_sql_survey % args1)
            self.cur.execute(self.insert_sql_info % args2)
            self.conn.commit()
            print u"两张表中的数据都插入成功"
        except Exception as e:
            print u"两张表中的数据都插入失败", e
            self.conn.rollback()

    def InsertSoftSurvery(self, res):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        print u"处理softsurvery字段信息"
        try:
            # print res
            self.cur.execute(self.insert_sql_survey % res)
            # print self.insert_sql_survey % res
            # 提交事务
            self.conn.commit()
        except Exception as e:
            print e
            print u"出现错误，回滚"
            self.conn.rollback()
        else:
            self.cur.close()

    def InsertSoftInfo(self, res):
        print u"处理softinfo字段信息"
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        try:
            self.cur.execute(self.insert_sql_info % res)
            # print self.insert_sql_info % res
            self.conn.commit()
        except Exception as e:
            print e
            print u"出现错误，回滚"
            # log_Main(u"出现错误，回滚", "info")
            self.conn.rollback()
        else:
            self.cur.close()

    def SelectUrl(self):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        self.cur.execute(self.select_sql1)
        return self.cur.fetchall()

    def DeletePageUrl(self, url):
        self.cur = self.conn.cursor()
        self.conn.select_db("appchina")
        try:
            self.cur.execute(self.delete_sql % url)
            self.conn.commit()
            print u"从数据库中删除成功"
        except Exception as e:
            print u"删除出错，回滚事务"
            self.conn.rollback()
        else:
            self.cur.close()

    def GetSoftInfo(self):
        self.cur = self.conn.cursor()
        self.cur.execute("select url from soft_info;")
        rows = self.cur.fetchall()
        return rows

    def GetSoftSurvey(self):
        self.cur = self.conn.cursor()
        self.cur.execute("select url from soft_survey;")
        rows = self.cur.fetchall()
        return rows

    def GetSoftClassification(self):
        self.cur = self.conn.cursor()
        self.cur.execute("select url from soft_classification;")
        rows = self.cur.fetchall()
        return rows

    def DeleteInfoUrls(self, url):
        self.cur = self.conn.cursor()
        try:
            print u"删除soft_info中的字段"
            self.cur.execute("delete from soft_info where url = '%s';" % url)
            self.conn.commit()
            print u"删除成功"
        except Exception as e:
            print e
            self.conn.rollback()

    def DeleteSurveyUrls(self, url):
        self.cur = self.conn.cursor()
        try:
            print u"删除soft_survey中的字段"
            self.cur.execute("delete from soft_survey where url = '%s';" % url)

            self.conn.commit()
            print u"删除成功"
        except Exception as e:
            print e
            self.conn.close()